/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package ddbserver.connections;

import ddbserver.common.ExceptionHandler;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Roar
 */
public class MySQLConnector {

    private String serverName;
    private int port;
    private String username;
    private String password;
    private String database;
    private static Connection connection;

    public MySQLConnector(String serverName, int port, String username, String password, String database) {
        this.serverName = serverName;
        this.port = port;
        this.username = username;
        this.password = password;
        this.database = database;
    }

    public boolean isConnected() {
        try {
            return connection.isValid(1000);
        } catch (Exception e) {
            ExceptionHandler.handleExcptin(e);
            return false;
        }
    }

    public int connect() {
        if (connection != null && isConnected()) {
            return 0;
        }
        connection = null;
        try {
            String driverName = "org.gjt.mm.mysql.Driver";
            Class.forName(driverName);
            String url = "jdbc:mysql://" + serverName + ":" + port + "/" + database; // a JDBC url
            connection = DriverManager.getConnection(url, username, password);
        } catch (Exception e) {
            ExceptionHandler.handleExcptin(e);
            return 1;
        }
        return 0;
    }

    public boolean executeSQL(String sql) {
        try {
            Statement stmt = connection.createStatement();
            return stmt.execute(sql);
        } catch (Exception e) {
            ExceptionHandler.handleExcptin(e);
            return false;
        }
    }

    public int executeUpdate(String sql) {
        try {
            Statement stmt = connection.createStatement();
            return stmt.executeUpdate(sql);
        } catch (Exception e) {
            ExceptionHandler.handleExcptin(e);
            return 1;
        }
    }

    public ResultSet executeQuery(String sql) {
        try {
            Statement stmt = connection.createStatement();
            return stmt.executeQuery(sql);
        } catch (Exception e) {
            ExceptionHandler.handleExcptin(e);
            return null;
        }
    }

    public boolean executeBatchInsert(String sql) {
        try {
            Statement stmt = connection.createStatement();
            stmt.execute(sql);
            return true;
        } catch (Exception e) {
            ExceptionHandler.handleExcptin(e);
        }
        return false;
    }

    public boolean executeBatchInsertSlow(String sql, Vector records) {
        String localSql = sql + "(";
        try {
            Statement stmt = connection.createStatement();
            for(int i = 0; i < records.size(); i++) {
                Vector record = (Vector)records.get(i);
                for(int j = 0; j < record.size(); j++) {
                    localSql = localSql + "" + record.get(j);
                    if(j != record.size() - 1) {
                        localSql = localSql + ",";
                    } else {
                        localSql = localSql + ")";
                    }
                }
                stmt.executeUpdate(localSql);
                localSql = sql + "(";
            }
        } catch (Exception e) {
            ExceptionHandler.handleExcptin(e);
        }
        return false;
    }

    public boolean executeBatchInsertVerySlow(String sql, Vector records) {
        PreparedStatement pstmt;
        try{
            pstmt = connection.prepareStatement(sql);            
            for(int i = 0; i < records.size(); i++) {
                Vector record = (Vector)records.get(i);
                for(int j = 0; j < record.size(); j++) {
                    pstmt.setObject((j + 1), record.get(j));
                }
                pstmt.execute();
            }
            return true;
        } catch (Exception e) {
            ExceptionHandler.handleExcptin(e);
        }
        return false;
    }
    
    public boolean executeInsert(String sql, Vector record) {
        PreparedStatement pstmt;
        try {
            pstmt = connection.prepareStatement(sql);
            for(int i = 0; i < record.size(); i++) {
                pstmt.setObject((i + 1), record.get(i));
            }
            pstmt.execute();
            return true;
        } catch (Exception e) {
            ExceptionHandler.handleExcptin(e);
        }
        return false;
    }
}
